﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace QuanLyDoiXe
{
    public partial class frmTestReport : Form
    {
        public frmTestReport()
        {
            InitializeComponent();
        }

        private void frmLapHoaDonGiaoHang_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'quanLyDoiXeDataSet.DataTable1' table. You can move, or remove it, as needed.
            this.dataTable1TableAdapter.Fill(this.quanLyDoiXeDataSet.DataTable1);
           // string strQuery = "select distinct hd.biensoxegh,hd.ngaygiaohang,hd.ngaylaphoadon,ct.soluong,dd.diachi,dd.nguoilienhe,lh.tenloaihang,tt.tentrangthai from chi_tiet_hoa_don_giao_hang ct,hoa_don_giao_hang hd,dia_diem dd,loai_hang lh,trang_thai tt where hd.mahoadongh = ct.mahoadongh and ct.madiadiem = dd.madiadiem and ct.loaihang = lh.maloaihang and ct.matrangthai = tt.matrangthai group by hd.biensoxegh,hd.ngaygiaohang,hd.ngaylaphoadon";
           // string strQuery = "select hd.mahoadongh from hoa_don_giao_hang hd group by hd.mahoadongh";
           //// string strQuery = "select * from chi_tiet_hoa_don_giao_hang ct,hoa_don_giao_hang hd,dia_diem dd,loai_hang lh,trang_thai tt where hd.mahoadongh = ct.mahoadongh and ct.madiadiem = dd.madiadiem and ct.loaihang = lh.maloaihang and ct.matrangthai = tt.matrangthai";

           // string strCnn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\USER\Desktop\DoiXeVinamilk-Alpenlibe\QuanLyDoiXe\Database\QuanLyDoiXe.accdb";
           // OleDbDataAdapter ad = new OleDbDataAdapter(strQuery, strCnn);

           // DataTable dt = new DataTable();
           // ad.Fill(dt);

           // rptTest rptLapDGH = new rptTest();
           // rptLapDGH.SetDataSource(dt);
           // crystalReportViewer1.ReportSource = rptLapDGH;

            //ORDER BY q.MaQDXC
            //string strQuery = "SELECT c.TenDuong, C.SoKmChay, c.XangTieuThu, c.ThoiGianDung, q.MaQDXC,q.BienSoXe, q.NgayChay FROM   CHI_TIET_QUANG_DUONG_XE_CHAY c INNER JOIN QUANG_DUONG_XE_CHAY q ON c.MaQDXC=q.MaQDXC ";
            //string strQuery = " SELECT ct.SoLuong, dd.DiaChi, dd.NguoiLienHe, hd.MaHoaDonGH, hd.NgayGiaoHang, hd.BienSoXeGH, hd.NgayLapHoaDon, lh.TenLoaiHang, tt.TenTrangThai FROM   (((CHI_TIET_HOA_DON_GIAO_HANG ct INNER JOIN DIA_DIEM dd ON ct.MaDiaDiem=dd.MaDiaDiem) INNER JOIN HOA_DON_GIAO_HANG hd ON ct.MaHoaDonGH=hd.MaHoaDonGH) INNER JOIN LOAI_HANG lh ON ct.LoaiHang=lh.MaLoaiHang) INNER JOIN TRANG_THAI tt ON ct.MaTrangThai=tt.MaTrangThai ORDER BY hd.MaHoaDonGH";

            //string strQuery = "SELECT * FROM   CHI_TIET_QUANG_DUONG_XE_CHAY c , QUANG_DUONG_XE_CHAY q , XE x,cap_nhien_lieu cnl,chi_tiet_cap_nhien_lieu ct where x.bienso = q.biensoxe and x.bienso = ct.biensoxe and ct.macapnl = cnl.macapnl and  c.MaQDXC=q.MaQDXC ";
            //string strQuery = "SELECT * FROM   CHI_TIET_QUANG_DUONG_XE_CHAY c , QUANG_DUONG_XE_CHAY q where c.MaQDXC=q.MaQDXC";
           // string strQuery = "SELECT q.biensoxe, sum(ct.dinhmuc) as [tongdinhmuc], sum(c.xangtieuthu) as tongxangtieuthu, sum(c.sokmchay)  as [quangduong] FROM   CHI_TIET_QUANG_DUONG_XE_CHAY c , QUANG_DUONG_XE_CHAY q ,chi_tiet_cap_nhien_lieu ct where q.biensoxe = ct.biensoxe and c.MaQDXC=q.MaQDXC  group by q.biensoxe";
            string strQuery = "SELECT       t.BienSoXe, t.tongxangtieuthu, t.quangduong, cn.dinhmuc FROM ((SELECT q.BienSoXe, SUM(c.XangTieuThu) AS tongxangtieuthu, SUM(c.SoKmChay) AS quangduong FROM (QUANG_DUONG_XE_CHAY q INNER JOIN CHI_TIET_QUANG_DUONG_XE_CHAY c ON q.MaQDXC = c.MaQDXC) GROUP BY q.BienSoXe) t INNER JOIN (SELECT  BienSoXe, SUM(DinhMuc) AS dinhmuc FROM  CHI_TIET_CAP_NHIEN_LIEU GROUP BY BienSoXe) cn ON cn.BienSoXe = t.BienSoXe)";
            string strCnn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\QuanLyDoiXe\Database\QuanLyDoiXe.accdb";
            OleDbDataAdapter ad = new OleDbDataAdapter(strQuery, strCnn);

            //DataTable dt = new DataTable();
            //ad.Fill(dt);

            QuanLyDoiXeDataSet qldx = new QuanLyDoiXeDataSet();

            ad.Fill(qldx, "DataTable1");

            CrystalReport2 rptBaoCaoQD = new CrystalReport2();
            rptBaoCaoQD.SetDataSource(qldx);
            crystalReportViewer1.ReportSource = rptBaoCaoQD;
        }
    }
}
